<?php

require("LITTLE_ExcelReader.class.php");

class LITTLE_ChukuExcelReader extends LITTLE_ExcelReader {
	
	private $fileId = null;
	
	public function __construct(){
		$this->tableName = 'little_chuku';
		$this->reader = new Spreadsheet_Excel_Reader();
		$this->reader->setUTFEncoder('iconv');
		$this->reader->setOutputEncoding('utf-8');
	}
	
	
	/**
	 * 将文件信息录入到数据库
	 * @return unknown_type
	 */
	function saveFileInfo(){
		global $file;
		$query = "insert into little_chuku_file set filename = '{$file->getFilename()}', uploadTime = now()";
		DB::query($query);
		$this->fileId = DB::insert_id();
	}
	
	/**
	 * 判断文件是否已经上传过
	 * 如果已经上传过，返回上回上传的时间
	 *
	 * @return date or boolean
	 */
	public function myHaveUploaded(){
		$query = "select filename, uploadTime from little_chuku_file where filename = '$this->filename'";
		$result = DB::query($query);
		if(mysqli_num_rows($result) >= 1){
			$row = $result->fetch_assoc();
			return $row['uploadTime'];
		}
		return false;
	}
	
	/**
	 * 检查第一行是否正确
	 * @throw LackSomeColumn
	 */
	public function checkMyFirstRow(){
		$lackedColumns = "";
		$unknownColumns = "";

		if(!isset($this->reader->sheets[0])){
			throw new ExcelMetaDataError();
		}

/*		foreach($this->obligatoColumns as $val){
			if(!in_array($val, $this->reader->sheets[0]['cells'][1]))
				$lackedColumns .= $val.',';
		}*/
		
		$chineseColumns = array_keys($this->tableColumns);
		foreach($this->reader->sheets[0]['cells'][1] as $col){
			if(!in_array($col, $chineseColumns)){
				$unknownColumns .= $col.',';
			}
		}

/*		if(!empty($lackedColumns)){
			throw new LackSomeColumn($lackedColumns);
		}*/
		if(!empty($unknownColumns)){
			throw new UnknownColumnIndex($unknownColumns);
		}
	}
	
	/**
	 * 找到未上传的产品质量证明书号
	 * @param $count
	 * @return unknown_type
	 */
	public function getMyHaveNotUploadedCertificationNumber($count = null){

		$cnArray = $this->getCertificationNumberArray();

		$cnArray = array_unique($cnArray);
		$oneHaveNot = $this->getMyOneHaveNotUploadedCertificationNumber($cnArray);


		if($oneHaveNot !== false){
			$haveNot = array($oneHaveNot);
			return $haveNot;
		}else{
			return null;
		}
	}
	
	/**
	 * 表格中产品质量证明书号与入库一样，但是产品质量证书编号不一样的情况
	 * 抛出异常，不允许上传
	 * @return unknown_type
	 */
	public function checkCertificationCodification(){
		$cnArray = $this->getCertificationNumberAndCertificationCodificationArray();
		$cns = array();
		foreach ($cnArray as $values){
			$mc =  $values[0];
			$cc =  $values[1];
			$query = "select certificationCodification from little_ruku where certificationNumber = '$mc'";
			$result = DB::query($query);
			while($row = $result->fetch_assoc()){
				if($row['certificationCodification']!=$cc){
					array_push($cns,$mc);
				}
			}
		}
		$cns = array_unique($cns);
		if(!empty($cns)){
			$cns = join(", ",$cns);
			$errMsg = "表格中如下产品质量证明书号与入库一样，但是产品质量证书编号不一样：<br/>".$cns;
			throw new ExistCollisionCodifications($errMsg);
		}
	}
/**
	 * 从数组$cnArray中获得一个还没有上传的产品质量证明书号
	 * 使用递归方式，因为只需要找到一个，所以可以使用二分查找法。
	 *
	 * @param array $cnArray
	 * @return string
	 */
	private function getMyOneHaveNotUploadedCertificationNumber($cnArray){
		$inString = "'".join("', '", $cnArray)."'";
		$query = "select count(distinct certificationNumber) from little_ruku where certificationNumber in($inString)";
		//echo "$query".'<br />';
		$result = DB::query($query);
		$row = $result->fetch_assoc();
		//echo "find row: ".$row['count(distinct certificationNumber)'].'<br />';
		//echo "array row: ".count($mcArray).'<br />';
		if($row['count(distinct certificationNumber)'] == count($cnArray)){
			return false;
		}else{
			if(count($cnArray) == 1){
				return $cnArray[0];
			}else{
				$cnArrayArray = array_chunk($cnArray, (count($cnArray)+1)/2);
				$firstHalf = $this->getOneHaveNotUploadedCertificationNumber($cnArrayArray[0]);
				if($firstHalf == false){
					$secondHalf = $this->getOneHaveNotUploadedCertificationNumber($cnArrayArray[1]);
					if($secondHalf == false){
						throw new CustomException("这里肯定有个错误， 因为这些产品质量证明书号中肯定有未上传的");
					}else{
						return $secondHalf;
					}
				}else{
					return $firstHalf;
				}
			}
		}
	}
	/**
	 * 将电子表格中的信息读取数据库，读入之前必须先设置
	 * $tableColumns
	 *
	 * @throws DBConnectException DBQueryException CustomException
	 */
	public function myReadIntoDB(){
		
		try{
			date_default_timezone_set('Asia/Shanghai');
			$currentTime = date('Y/m/d H:i:s');
	
			$valuesStr = "";
			//dprint($this->reader->sheets[0]['cells']);
			for($i = 2; $i <= $this->reader->sheets[0]['numRows']; $i++){
				if(!isset($this->reader->sheets[0]['cells'][$i])){
					continue;
				}
				try{
				$formatedSheetRow = $this->getFormatedFromSheetRow($this->reader->sheets[0]['cells'][$i]);
				}catch(CustomException $e){
					//var_dump($formatedSheetRow);
					throw new CustomException($e.'Excel表格出错行数：'.$i);
				}
				if($formatedSheetRow == null){
					continue;
				}
	
				//下面对数据进行过滤
				foreach($formatedSheetRow as $key => $val){
					$formatedSheetRow[$key] = Filter::forDBInsertion($val);
				}
	
				//$value = ", ('".join("', '", $formatedSheetRow)."','{$this->fileId}')\n";
				
				$value = "";
				foreach($formatedSheetRow as $k => $v){
					if(empty($v)){
						$value .= 'null, ';
					}else{
						$value .= "'$v', ";
					}
				}
				$value = ", (".$value."'{$this->fileId}')\n";
				$valuesStr .= $value;
			}
	
			$valuesStr = substr($valuesStr, 1);
	
			$cols = " (`".join("`, `", array_unique(array_values($this->tableColumns)))."`, `fileId`) ";
			$query = "insert into {$this->tableName}$cols values $valuesStr;";
	
				DB::query($query);
		}catch(Exception $e){
			throw $e;
		}
	}
}

?>